# -!- coding: utf-8 -!-
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
获取中移动项目的运营统计数据:
1.授信成功、失败、汇总信息； 2.借款成功、失败、汇总信息；3.办理业务成功记录
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

from msydwork.prod.pro_config import cursor
from pycacho.cachobase.deal_time import minus_date
from pycacho.cachobase.deal_excel import copy_new_sheet,copy_update_sheet,common_write
from pycacho.cachobase.deal_float import float_deal


# 授信数据,当天日期
def get_applyInfo(count,countC):
    sql = 'select "%s","apply","P",count(*),sum(credit_amt) from pro_main where workflow_type="C" and status="P" AND substr(create_time,1,10)%s"%s" ' \
          'UNION select "%s","apply","R",count(*),sum(credit_amt) from pro_main where workflow_type="C" and status="R" AND substr(create_time,1,10)%s"%s";' % (td_date,count,td_date,td_date,countC,td_dateC)
    cursor.execute(sql)
    results = cursor.fetchall()
    info = []
    for result in results:
        row = []
        for data in result:
            row.append(data)          # 多条数据
        info.append(row)
    #print(info)
    return info


# 授信数据,当天日期
def get_freezeInfo(count,countC):
    sql = 'select "%s","freeze","P",count(*),sum(loan_amt) from pro_main where workflow_type="L" and status="P" and substr(create_time,1,10)%s"%s" ' \
          'UNION select "%s","freeze","R",count(*),sum(loan_amt) from pro_main where workflow_type="L" and status="R" and substr(create_time,1,10)%s"%s";' % (td_date,count,td_date,td_date,countC,td_dateC)
    cursor.execute(sql)
    results = cursor.fetchall()
    info = []
    for result in results:
        row = []
        for data in result:
            row.append(data)           # 多条数据
        info.append(row)
    #print(info)
    return info


# 办理套餐成功数据,当天日期
def get_blockInfo(count):
    sql = 'select "%s","block","接口",count(*),sum(loan_amt) from pkg_notice,pro_main where pkg_notice.process_no=pro_main.process_no and substr(acp_tm,1,10)%s"%s";' % (td_date,count,td_date)
    cursor.execute(sql)
    results = cursor.fetchall()
    info = []
    for result in results:
        for data in result:
            info.append(data)            # 一条数据
    #print(info)
    return info


# 分省业务办理确认文件数据,昨天日期
def get_fileInfo(count):
    sql = 'select "%s","block","文件",count(*),sum(amt) from hb_loanfile_detail where substr(acp_dt,1,10)%s"%s";' % (yd_date,count,yd_date)
    cursor.execute(sql)
    results = cursor.fetchall()
    info = []
    for result in results:
        for data in result:
            info.append(data)           # 一条数据
    #print(info)
    return info


def main(count):
    file_path = 'E:\zenglingwei\statistics\\' + '中移动统计'
    countC = count
    two_list = []
    # 多条数据
    applyInfo = get_applyInfo(count,countC)
    freezeInfo = get_freezeInfo(count,countC)
    blockInfo = get_blockInfo(count)
    fileInfo = get_fileInfo(count)
    if count == '<=':
        two_list.append(['办理日期','流程','备注','订单数量','金额','百分比','单笔金额'])
    # 授信统计
    apply_s_count = applyInfo[0][3]  # 成功授信数量
    apply_f_count = applyInfo[1][3]  # 失败授信数量
    apply_amt = applyInfo[0][4]
    try:
        apply_s_rate = float_deal(apply_s_count / (apply_s_count + apply_f_count) * 100)
        apply_each_amt = float_deal(apply_amt / apply_s_count)
    except Exception as e:
        print(e)
        apply_s_rate = None
        apply_each_amt = None
    applyInfo[0].append(apply_s_rate)
    applyInfo[0].append(apply_each_amt)

    # 借款统计
    freez_s_count = freezeInfo[0][3]  # 成功借款数量
    freez_f_count = freezeInfo[1][3]  # 失败借款数量
    freez_amt = freezeInfo[0][4]
    try:
        freez_s_rate = float_deal(freez_s_count / (freez_s_count + freez_f_count) * 100)
        freez_each_amt = float_deal(freez_amt / freez_s_count)
    except Exception as e:
        print(e)
        freez_s_rate = None
        freez_each_amt = None
    freezeInfo[0].append(freez_s_rate)
    freezeInfo[0].append(freez_each_amt)

    # 套餐办理成功接口通知数量统计
    block_count = blockInfo[3]          # 接口通知成功借款数量
    block_amt = blockInfo[4]            # 接口通知成功借款金额
    file_count = fileInfo[3]            # 文件推送成功借款数量
    file_amt = fileInfo[4]              # 文件通知成功借款金额
    try:
        block_rate = float_deal(block_count / file_count * 100)
        block_each_amt = float_deal(block_amt / block_count)
        file_each_amt = float_deal(file_amt / file_count)
    except Exception as e:
        print(e)
        block_rate = None
        block_each_amt = None
        file_each_amt = None
    blockInfo.append(block_rate)
    blockInfo.append(block_each_amt)
    fileInfo.append(None)
    fileInfo.append(file_each_amt)

    for info in applyInfo:
        two_list.append(info)
    for info in freezeInfo:
        two_list.append(info)
    # 一条数据
    two_list.append(blockInfo)
    two_list.append(fileInfo)
    for info in two_list:
        print(info)
    if count=='<=':
        file = sheet_name + '汇总' + '.xls'
        old_file = old_name + '汇总' + '.xls'
        try:
            copy_update_sheet(two_list,file_path,old_file,file,sheet_name,0,0)
        except:
            copy_new_sheet(two_list,file_path,old_file,file,sheet_name,0,0)
    else:
        file = sheet_name + '.xls'
        old_file =  old_name + '.xls'
        try:
            copy_update_sheet(two_list,file_path,old_file,file,sheet_name,0,0)
        except:
            common_write(two_list,file_path,file,sheet_name,0,0)


if __name__ == '__main__':
    t = 0  # t=0 截止到今天，t=1截止到昨天
    td_date = minus_date(t,'t')  # 当天日期%Y-%m-%d
    td_dateC = td_date
    yd_date = minus_date(t+1,'t')  # 昨天日期%Y-%m-%d

    sheet_name = minus_date(t,'s')  # '%Y%m%d'
    old_name = minus_date(t + 1,'s')  # '%Y%m%d'

    if t == 0:              # 查询当天的
        main('=')
    else:
        main('<=')
        main('=')